/**
 * @author khathirdstep1025
 * Utility class, create and manage connections to database.
 * Copyright (c) 2013 - 2014
 */

package com.kha;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Database {

	// Fields.
	private String connectionString = null;
	private String driver = null;

	// Constructor.
	public Database(String connectionString, String driver) {

		this.connectionString = connectionString;
		this.driver = driver;
	}

	// Get connection object.
	private Connection getConnection() throws SQLException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {

		Class.forName(driver).newInstance();
		Connection conn = DriverManager.getConnection(connectionString);

		return conn;
	}

	private void closeConnection(Connection conn) throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}

	public ArrayList<Trainee> getTraineeList() throws SQLException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {

		// Get connection with connection string.
		Connection conn = getConnection();

		if (conn != null) {
			// Get list of trainees.

			ArrayList<Trainee> list = new ArrayList<Trainee>();

			// Get connection with connection string.
			ResultSet result = null;
			Statement stmt = null;

			// Execute SQL statement.
			stmt = conn.createStatement();

			result = stmt.executeQuery("SELECT * FROM " + "trainee" + ";");

			// Get result and populate to a list.
			while (result.next()) {
				Trainee item = new Trainee();

				item.setAccount(result.getString("account"));
				item.setFirstname(result.getString("firstname"));
				item.setLastname(result.getString("lastname"));
				item.setEmail(result.getString("email"));

				list.add(item);
			}

			if (result != null) {
				result.close();
			}
			if (stmt != null) {
				stmt.close();
			}

			closeConnection(conn);

			return list;

		} else {

			return null;

		}
	}

	// Count the record meets specify condition in a table.
	public int Count(String value, String column, String table)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {

		// SQL Statement to be executed
		Statement stmt = null;
		int count = 0;
		ResultSet result = null;

		Connection conn = getConnection();

		stmt = conn.createStatement();
		String statement = "SELECT COUNT(" + column + ") FROM " + table
				+ " WHERE " + column + " = '" + value + "';";

		result = stmt.executeQuery(statement);

		// Get the count.
		while (result.next()) {
			count = result.getInt(1);
		}

		result.close();
		stmt.close();

		return count;
	}

	// Update existed record in table.
	public void Update(Trainee trainee) throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException {

		Connection conn = getConnection();

		// Prepare SQL statement to be executed.
		String statement = "UPDATE trainee SET firstname=?, lastname=?, email=? WHERE account=?;";
		PreparedStatement pstmt = null;

		// Use to advoid SQL Injection.
		pstmt = conn.prepareStatement(statement);

		pstmt.setString(4, trainee.getAccount());
		pstmt.setString(1, trainee.getFirstname());
		pstmt.setString(2, trainee.getLastname());
		pstmt.setString(3, trainee.getEmail());

		pstmt.executeUpdate();

		pstmt.close();

	}

	// Save new record to table.
	public void Save(Trainee trainee) throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException {

		Connection conn = getConnection();

		// SQL Statement to be executed.

		String statement = "INSERT INTO trainee(account, firstname, lastname, email) VALUES (?, ?, ?, ?);";
		PreparedStatement pstmt = null;

		// Use to advoid SQL Injection
		pstmt = conn.prepareStatement(statement);

		pstmt.setString(1, trainee.getAccount());
		pstmt.setString(2, trainee.getFirstname());
		pstmt.setString(3, trainee.getLastname());
		pstmt.setString(4, trainee.getEmail());

		pstmt.executeUpdate();

		pstmt.close();

	}

}